/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataAccess;

import Entity.Items;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Home
 */
public class Item {

    public static boolean insertItem(Entity.Items it) {
        String sql = "insert into Items values(?, ?, ?, ?, ?, ?, ?)";
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareStatement(sql);
            pst.setInt(1, it.getCategoryID());
            pst.setString(2, it.getItemName());
            pst.setInt(3, it.getQuantityAvailable());
            pst.setFloat(4, it.getPrice());
            pst.setString(5, it.getDescript());
            pst.setString(6, it.getManufacture());
            pst.setBoolean(7, it.isStatus());
            return (pst.executeUpdate() == 1);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return false;
        }
    }

    public static boolean updateItem(Entity.Items it) {
        String sql = "update Items set CategoryID=?, ItemName=?, QuantityAvailable=?, Price=?, Descript=?, Manufacture=?, Status=? where ItemID=?";
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareStatement(sql);

            pst.setInt(1, it.getCategoryID());
            pst.setString(2, it.getItemName());
            pst.setInt(3, it.getQuantityAvailable());
            pst.setFloat(4, it.getPrice());
            pst.setString(5, it.getDescript());
            pst.setString(6, it.getManufacture());
            pst.setBoolean(7, it.isStatus());
            pst.setInt(8, it.getItemsID());
            return (pst.executeUpdate() == 1);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return false;
        }
    }

        public static boolean updateItemQuantityReceipt(Entity.BillSellDetails billD) {
        String sql = "update Items set QuantityAvailable=QuantityAvailable+? where ItemID=?";
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareStatement(sql);
            pst.setInt(1, billD.getQuantityReceipt());
            pst.setInt(2, billD.getItemID());
            return (pst.executeUpdate() == 1);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            return false;
        }
    }

    public static void deleteItem(String id) {
        String sql = "delete from Items where ItemID='" + id + "'";
        Statement st;
        try {
            st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement();
            st.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public static Entity.Items getByCode(String code) {
        String sql = "select * from Items where ItemID=?";
        Entity.Items it = null;
        try {
            PreparedStatement pst = DBUtil.getConnection(DBUtil.getInfoDB()).prepareStatement(sql);
            pst.setString(1, code);
            ResultSet rsItems = pst.executeQuery();
            if (!rsItems.next()) {
                return null;
            }
            it = new Items();
            it.setItemsID(rsItems.getInt("ItemID"));
            it.setCategoryID(rsItems.getInt("CategoryID"));
            it.setItemName(rsItems.getString("ItemName"));
            it.setQuantityAvailable(rsItems.getInt("QuantityAvailable"));
            it.setPrice(rsItems.getFloat("Price"));
            it.setDescript(rsItems.getString("Descript"));
            it.setManufacture(rsItems.getString("Manufacture"));
            it.setStatus(rsItems.getBoolean("Status"));
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return it;
    }
       public static ArrayList<Entity.Items> getAll() {
        ArrayList<Entity.Items> items = new ArrayList<Items>();
        String sql = "select * from Items";
        Entity.Items it = null;
        try {
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement();
            ResultSet rsItems = st.executeQuery(sql);
            while(rsItems.next()){
            it = new Items();
            it.setItemsID(rsItems.getInt("ItemID"));
            it.setCategoryID(rsItems.getInt("CategoryID"));
            it.setItemName(rsItems.getString("ItemName"));
            it.setQuantityAvailable(rsItems.getInt("QuantityAvailable"));
            it.setPrice(rsItems.getFloat("Price"));
            it.setDescript(rsItems.getString("Descript"));
            it.setManufacture(rsItems.getString("Manufacture"));
            it.setStatus(rsItems.getBoolean("Status"));
            items.add(it);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return items;
    }

        public static boolean readToTableItem(String sql, JTable tableName) {
        try {
            int nCount = 0;
            DefaultTableModel dm = (DefaultTableModel) tableName.getModel();
            for (int i = dm.getRowCount() - 1; i >= 0; i--) {
                dm.removeRow(i);
            }
            Vector cols = new Vector();
            Statement st = DBUtil.getConnection(DBUtil.getInfoDB()).createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rm = rs.getMetaData();
            for (int i = 1; i <= rm.getColumnCount(); i++) {
                cols.add(rm.getColumnName(i));
            }
            dm.setColumnIdentifiers(cols);
            while (rs.next()) {
                Vector rows = new Vector();
//                for (int i = 1; i <= rm.getColumnCount(); i++) {
//                    rows.add(rs.getString(i));
//                    nCount++;
//                }
                
                rows.addElement(rs.getInt(1));
                rows.addElement(rs.getString(2));
                rows.addElement(rs.getString(3));
                rows.addElement(rs.getInt(4));
                rows.addElement(rs.getFloat(5));
                rows.addElement(rs.getString(6));
                rows.addElement(rs.getString(7));
                rows.addElement(rs.getString(8));
                rows.addElement(rs.getBoolean(9));

                dm.addRow(rows);
            }
            tableName.setModel(dm);
            if (nCount > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(Item.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }
}
